﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using WF_QLThuVien.DataLayer;
using WF_QLThuVien.Entities.PhieuMuon;
using WF_QLThuVien.Entities;
using WF_QLThuVien.Entities.Sach;

namespace WF_QLThuVien.DataLayer.PhieuMuon
{
    class CDAL_PhieuMuon
    {
        DataAccessHelper db = new DataAccessHelper();

        public bool Insert(CPhieuMuon pm)
        {
            try
            {
                int count = db.ExecuteNonQuery("INSERT INTO PhieuMuon VALUES"
                    +"('" + pm.PhieuMuonID + "','" + pm.DocGiaID + "',#" + pm.NgayLap + "#," + pm.CtPhieuMuon+ ")");
                return count > 0;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_PhieuMuon.Insert" + ex.ToString());
            }
        }

        public CPhieuMuon SearchPhieuMuonById(string id)
        {
            try
            {
                CPhieuMuon pm = (CPhieuMuon)db.GetValue("SELECT * FROM PhieuMuon WHERE PhieuMuonID=" + id);
                return pm;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_PhieuMuon.SearchPhieuMuonById" + ex.ToString());
            }
        }

        private List<CPhieuMuon> GetList(string cmd)
        {
            DataTable dt = db.GetDataTable(cmd);
            List<CPhieuMuon> lst = new List<CPhieuMuon>();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(new CPhieuMuon(r));
            }
            return lst;
        }

        public List<CPhieuMuon> GetListAll()
        {
            try
            {

                string cmd = "SELECT * FROM PhieuMuon";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_PhieuMuon.GetListAll" + ex.ToString());
            }
        }

        public List<CPhieuMuon> GetListBySachID(string id)
        {
            try
            {
                string cmd = "SELECT PhieuMuon.PhieuMuonID, PhieuMuon.DocGiaID, NgayLap FROM PhieuMuon, ChiTietPhieuMuon WHERE PhieuMuon.PhieuMuonID=ChiTietPhieuMuon.PhieuMuonID and ChiTietPhieuMuon.SachID=" + id;
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_PhieuMuon.GetListBySachID" + ex.ToString());
            }
        }

        public List<CPhieuMuon> GetListByTenSach(string name)
        {
            try
            {
                string cmd = "SELECT PhieuMuon.PhieuMuonID, PhieuMuon.DocGiaID, NgayLap FROM PhieuMuon, ChiTietPhieuMuon, Sach, DauSach WHERE PhieuMuon.PhieuMuonID=ChiTietPhieuMuon.PhieuMuonID and ChiTietPhieuMuon.SachID=Sach.SachID and DauSach.DauSachID=Sach.DauSachID and TenSach like'" + name + "'";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_PhieuMuon.GetListByTenSach" + ex.ToString());
            }
        }

        public List<CPhieuMuon> GetListByDocGiaID(string id)
        {
            try
            {
                string cmd = "SELECT * FROM PhieuMuon WHERE DocGiaID=" + id;
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_PhieuMuon.GetListByDocGiaID" + ex.ToString());
            }
        }

        public List<CPhieuMuon> GetListByTenDocGia(string name)
        {
            try
            {
                string cmd = "SELECT PhieuMuon.PhieuMuonID, PhieuMuon.DocGiaID, NgayLap FROM PhieuMuon, DocGia WHERE DocGia.TenDocGia like'" + name + "' and DocGia.DocGiaID=PhieuMuon.DocGiaID";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_PhieuMuon.GetListByTenDocGia" + ex.ToString());
            }
        }

        public List<CPhieuMuon> GetListByNgayLap(DateTime d)
        {
            try
            {
                string cmd = "SELECT * FROM PhieuMuon WHERE NgayLap=" + d;
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_PhieuMuon.GetListByNgayLap" + ex.ToString());
            }
        }
    }
}
